package com.myWeb.dao;

import com.myWeb.entity.Question;
import com.myWeb.util.JdbcUtil;
import javax.servlet.http.HttpServletRequest;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class QuestionDao {

    private JdbcUtil util = new JdbcUtil();

    public int add(Question question,HttpServletRequest request){

        String sql = "insert into question(title,questionA,questionB,questionC,questionD,answer) values(?,?,?,?,?,?)";
        int result = 0;
        PreparedStatement ps = null;
        try {
            ps = util.createStatement(sql,request);
            ps.setString(1,question.getTitle());
            ps.setString(2,question.getQuestionA());
            ps.setString(3,question.getQuestionB());
            ps.setString(4,question.getQuestionC());
            ps.setString(5,question.getQuestionD());
            ps.setString(6,question.getAnswer());
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(request);
        }
        return result;
    }

    public List findAll(HttpServletRequest request){

        String sql = "select * from question";
        List questionList = new ArrayList();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            ps = util.createStatement(sql,request);
            rs = ps.executeQuery();
            while(rs.next()){
                Integer questionId = rs.getInt("questionId");
                String title = rs.getString("title");
                String questionA = rs.getString("questionA");
                String questionB = rs.getString("questionB");
                String questionC = rs.getString("questionC");
                String questionD = rs.getString("questionD");
                String answer = rs.getString("answer");
                Question question = new Question(questionId,title,questionA,questionB,questionC,questionD,answer);
                questionList.add(question);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(request);
        }
        return questionList;

    }

    public int delete(int questionId, HttpServletRequest request){

        String sql = "delete from question where questionId = ?";
        int result = 0;
        PreparedStatement ps = null;
        try{
            ps = util.createStatement(sql,request);
            ps.setInt(1,questionId);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(request);
        }
        return result;
    }

    public Question findById(int questionId,HttpServletRequest request){

        String title, questionA,questionB,questionC,questionD,answer;
        String sql = "select * from question where questionId = ?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        Question question = null;
        try{
            ps = util.createStatement(sql,request);
            ps.setInt(1,questionId);
            rs = ps.executeQuery();
            while(rs.next()) {
                title = rs.getString("title");
                questionA = rs.getString("questionA");
                questionB = rs.getString("questionB");
                questionC = rs.getString("questionC");
                questionD = rs.getString("questionD");
                answer = rs.getString("answer");
                question = new Question(questionId, title, questionA, questionB, questionC, questionD, answer);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(request);
        }
        return question;
    }

    public int update(Question question, HttpServletRequest request){

        String sql = "update question set title=?, questionA=?, questionB=?, questionC=?, questionD=?, answer=?" +
                "where questionId=?";
        PreparedStatement ps = null;
        int result = 0;
        try{
            ps = util.createStatement(sql,request);
            ps.setString(1,question.getTitle());
            ps.setString(2,question.getQuestionA());
            ps.setString(3,question.getQuestionB());
            ps.setString(4,question.getQuestionC());
            ps.setString(5,question.getQuestionD());
            ps.setString(6,question.getAnswer());
            ps.setInt(7,question.getQuestionId());
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(request);
        }
        return result;
    }

    public List findRand(HttpServletRequest request){

        String sql = "select * from question order by rand() limit 0,5";
        List<Question> questionList = new ArrayList<>();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            ps = util.createStatement(sql,request);
            rs = ps.executeQuery();
            while(rs.next()){
                Integer questionId = rs.getInt("questionId");
                String title = rs.getString("title");
                String questionA = rs.getString("questionA");
                String questionB = rs.getString("questionB");
                String questionC = rs.getString("questionC");
                String questionD = rs.getString("questionD");
                String answer = rs.getString("answer");
                Question question = new Question(questionId,title,questionA,questionB,questionC,questionD,answer);
                questionList.add(question);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            util.close(request);
        }
        return questionList;
    }

}
